package edu.isquen.core.form.dao;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import edu.isquen.core.form.model.Column;
import edu.isquen.core.form.model.Form;
import edu.isquen.core.form.model.Table;
import edu.isquen.core.form.model.element.FormField;

public class SQLBuilder {
	public final static String SQL_DELIMITER = ";";

	protected Map<Object, Object> typeNames = new HashMap<Object, Object>();

	protected StringBuffer rtnSql;
	
	protected String schema="isquen_test";
	
	protected Form form=null;
	
	protected Table table=null;

	public SQLBuilder(Form form){
		this.form=form;
		this.table=createTable(form);
		registerColumnType(Types.VARCHAR, "VARCHAR(200)");
		registerColumnType(Types.LONGVARCHAR, "VARCHAR(4000)");
		registerColumnType(Types.NUMERIC, "DECIMAL(22,5)");
		registerColumnType(Types.INTEGER, "INT");
		registerColumnType(Types.BIT, "BIT(1)");
		registerColumnType(Types.DATE, "DATE");
		registerColumnType(Types.TINYINT, "TINYINT");
		registerColumnType(Types.BLOB, "MEDIUMBLOB");
		registerColumnType(Types.TIMESTAMP, "DATETIME");
		registerColumnType(Types.BOOLEAN, "BOOLEAN");
		registerColumnType(Types.CLOB, "MEDIUMTEXT");
	}
	
	private void registerColumnType(int code, String name) {
		typeNames.put(Integer.valueOf(code), name);
	}
	
	public Table createTable(Form form) {
		if (form != null) {
			Table table = new Table("K_"+form.getName());
			table.setFormName(form.getName());
			table.getColumns().addAll(this.getDocCommColumns());
			
			for (Iterator<FormField> iter = form.getValueStoreFields().iterator(); iter.hasNext();) {
				FormField field = (FormField) iter.next();
				table.getColumns().add(createColumn(field));
			}
			return table;
		}
		return null;
	}
	
	public Column createColumn(FormField field) {
		if (field != null) {
			String columnName="ITEM_"+field.getName();
			
			String id = field.getId();
			Column column = new Column(id, columnName, Types.VARCHAR);
			column.setFieldName(field.getName());
			return column;
		}
		return null;
	}
	
	private Collection<Column> getDocCommColumns() {
		Collection<Column> rtn = new ArrayList<Column>();
		
		Column id = new Column("", "ID", Types.VARCHAR);
		id.setPrimaryKey(true);
		Column name = new Column("", "NAME", Types.VARCHAR);
		Column description = new Column("", "DESCRIPTION", Types.VARCHAR);
		
		Column formId = new Column("", "FORMID", Types.VARCHAR);
		Column formName = new Column("", "FORMNAME", Types.VARCHAR);
		
		Column author = new Column("", "AUTHOR", Types.VARCHAR);
		Column createDate = new Column("", "CREATEDATE", Types.TIMESTAMP);
		
		Column lastModifyUser = new Column("", "LASTMODIFYUSER", Types.VARCHAR);
		Column lastModifyDate = new Column("", "LASTMODIFYDATE", Types.TIMESTAMP);
		
		Column editAble = new Column("", "EDITABLE", Types.BIT);
		
		rtn.add(id);
		rtn.add(name);
		rtn.add(description);
		
		rtn.add(formName);
		rtn.add(formId);
		
		rtn.add(createDate);
		rtn.add(author);
		
		rtn.add(lastModifyUser);
		rtn.add(lastModifyDate);
		
		rtn.add(editAble);
		return rtn;
	}
	
	protected String getSqlTypeName(Integer code) {
		return (String) typeNames.get(code);
	}

	protected String getTableFullName(Table table) {
		if (schema != null && schema.trim().length() > 0) {
			String tableFullName = schema + "." + table.getName();
			return tableFullName;
		}
		return table.getName();
	}

	protected String getTableFullName(String tableName) {
		String rtn = tableName;
		if (schema != null && schema.trim().length() > 0) {
			String tableFullName = schema + "." + tableName;
			rtn = tableFullName;
		}
		return rtn.toUpperCase();
	}

	public void setWriter(StringBuffer writer) {
		this.rtnSql = writer;
	}

	public void createTableAsSelect(Table table, Table changedTable, boolean dropFirst) {
		String tableFullName = getTableFullName(table);

		rtnSql = new StringBuffer();
		rtnSql.append("create table " + tableFullName);
		rtnSql.append(" AS SELECT "+getColumnNameString(table)+" FROM " + changedTable.getName());
		rtnSql.append(SQL_DELIMITER);
	}
	
	private String getColumnNameString(Table table) {
		StringBuffer buffer = new StringBuffer();

		Collection<Column> columns = table.getColumns();
		if (columns != null && !columns.isEmpty()) {
			for (Iterator<Column> iter = columns.iterator(); iter.hasNext();) {
				Column column = (Column) iter.next();

				buffer.append(column.getName());
				buffer.append(",");
			}
			buffer.deleteCharAt(buffer.length() - 1);
		}

		return buffer.toString();
	}
	
	private String getColumnString(Table table) {
		StringBuffer buffer = new StringBuffer();

		Collection<Column> columns = table.getColumns();
		if (columns != null && !columns.isEmpty()) {
			for (Iterator<Column> iter = columns.iterator(); iter.hasNext();) {
				Column column = (Column) iter.next();
				int code = column.getTypeCode();

				buffer.append(column.getName());
				buffer.append(" " + getSqlTypeName(Integer.valueOf(code)));
				if (column.isPrimaryKey()) {
					buffer.append(" NOT NULL PRIMARY KEY");
				}
				buffer.append(",");
			}
			buffer.deleteCharAt(buffer.length() - 1);
		}

		return buffer.toString();
	}
	
	public void buildDropTableSql() {
		rtnSql = new StringBuffer();
		rtnSql.append("drop table " + getTableFullName(table));
		rtnSql.append(SQL_DELIMITER);
	}
	
	public void buildCreateTableSql() {
		String tableFullName = getTableFullName(table);

		rtnSql = new StringBuffer();

		rtnSql.append("create table " + tableFullName + "(");
		rtnSql.append(getColumnString(table));
		rtnSql.append(")");
		rtnSql.append(SQL_DELIMITER);
	}


	public void createTable(Table table) {
		String tableFullName = getTableFullName(table);

		rtnSql = new StringBuffer();

		rtnSql.append("create table " + tableFullName + "(");
		rtnSql.append(getColumnString(table));
		rtnSql.append(")");
		rtnSql.append(SQL_DELIMITER);
	}

	public void addColumn(Table changedTable, Column targetColumn) {
		String tableFullName = getTableFullName(changedTable);
		String colName = targetColumn.getName();
		Integer typeCode = Integer.valueOf(targetColumn.getTypeCode());

		rtnSql.append("alter table " + tableFullName + " ");
		rtnSql.append("add " + colName + " " + getSqlTypeName(typeCode));
		rtnSql.append(SQL_DELIMITER);
	}

	public void columnDataCopy(Table changedTable, Column sourceColumn, Column targetColumn) {
		String tableFullName = getTableFullName(changedTable);

		rtnSql.append("update " + tableFullName + " ");
		rtnSql.append("set " + targetColumn + " = " + sourceColumn);
		rtnSql.append(SQL_DELIMITER);
	}

	public void modifyColumnDataType(Table changedTable, Column targetColumn) {
		String tableFullName = getTableFullName(changedTable);
		String targetColumnName = targetColumn.getName();
		String targetTypeName = getSqlTypeName(Integer.valueOf(targetColumn.getTypeCode()));

		rtnSql.append("alter table " + tableFullName);
		rtnSql.append(" modify " + targetColumnName);
		rtnSql.append(" " + targetTypeName);
		rtnSql.append(SQL_DELIMITER);
	}

	public void columnRename(Table changedTable, Column sourceColumn, Column targetColumn) {
		String tableFullName = getTableFullName(changedTable);

		rtnSql.append("alter table " + tableFullName);
		rtnSql.append(" rename column " + sourceColumn.getName());
		rtnSql.append(" to " + targetColumn.getName());
		rtnSql.append(SQL_DELIMITER);
	}

	public void dropColumn(Table changedTable, Column sourceColumn) {
		String tableFullName = getTableFullName(changedTable);

		rtnSql.append("alter table " + tableFullName);
		rtnSql.append(" drop column " + sourceColumn.getName());
		rtnSql.append(SQL_DELIMITER);
	}

	
	public void tableDataCopy(Table sourceTable, Table targetTable) {
		String sourceTableName = getTableFullName(sourceTable);
		String targetTableName = getTableFullName(targetTable);

		rtnSql.append("insert into " + targetTableName);
		rtnSql.append(" select * from " + sourceTableName);
		rtnSql.append(SQL_DELIMITER);
	}

	
	public void tableRename(Table changedTable, Table targetTable) {
		String changedTableName = getTableFullName(changedTable);
		String targetTableName = getTableFullName(targetTable);

		rtnSql.append("alter table " + changedTableName);
		rtnSql.append(" rename to " + targetTableName);
		rtnSql.append(SQL_DELIMITER);

	}

	public String getSQL() {
		return rtnSql.toString();
	}
	
	public Form getForm() {
		return form;
	}

	public void setForm(Form form) {
		this.form = form;
	}
}
